<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of CDataReportProvider
 *
 * @author Dell
 */
class CDataReportProvider extends CApplicationComponent {

//put your code here

    public function getContohData() {
        return array(3, 4, 4, 5);
    }

	public function getSelfAssesmentReport($userMember, $assesment)
	{
		$sql = "select * from assessment";
		$this->connection=Yii::app()->db;  

		$dbCmd = $this->connection->createCommand($sql);

        $data = $dbCmd->queryAll();


		$dataProvider=new CArrayDataProvider($data, array(
			'id'=> 'dataAssesment',
			'keyField'=>'ASSESSMENT_ID',
//			'sort'=>array(
//				'attributes'=>array(
//					 'ASSESSMENT_ID', 'TITLE', 'DESCRIPTION','DATE'
//				),
//			),
			'pagination'=>array(
				'pageSize'=>10,
			),
		));

		return $dataProvider;
	}

	public function getAssessmentReport($assessment_id, $employee_id, $job_title_id)
	{
		$connection = Yii::app()->db;  

		$data = array();

		$data['assessment'] = CDataReportProvider::get_assessment_by_id($assessment_id);
		$assessment_member = CDataReportProvider::get_assessment_member_for_employee($assessment_id, $employee_id, $job_title_id);

		foreach($assessment_member as $i=>$employee)
		{
			// for testing purposes only
			$include_employee = false;
			$include_verifier = false;
			
			if($employee['ASSESSMENT_TYPE'] == 1 and !empty($employee['EMPLOYEE_ID']))
			{
				$data['employee']['employee_id'] = $employee['EMPLOYEE_ID'];
			}
			elseif($employee['ASSESSMENT_TYPE'] == 2 and !empty($employee['ASSESSOR_EMPLOYEE_ID']))
			{
				$data['assessor']['employee_id'] = $employee['ASSESSOR_EMPLOYEE_ID'];
			}
			elseif($employee['ASSESSMENT_TYPE'] == 3 and !empty($employee['ASSESSOR_EMPLOYEE_ID']) and $include_verifier)
			{
				$data['verifier']['employee_id'] = $employee['ASSESSOR_EMPLOYEE_ID'];
			}
			$member[$employee['ASSESSMENT_TYPE']] = $employee['ASSMNT_MBR_ID'];
		}

		$report = array();
		$data['employee'] = CDataReportProvider::get_employee_by_employee_id($data['employee']['employee_id']);

		if(!empty($data['employee']['employee_id']) and isset($member[1]) and $include_employee)
		{
			$data['employee']['assessment_member_id'] = $member[1];
			$data['employee']['assessment_result'] = CDataReportProvider::get_assessment_result_by_assessment_member_id($member[1]);
			$report = CDataReportProvider::calculate_result($data['employee']['assessment_result'], $report, 'employee');
		}
		if(!empty($data['assessor']['employee_id']))
		{
			$data['assessor'] = CDataReportProvider::get_employee_by_employee_id($data['assessor']['employee_id']);
			$data['assessor']['assessment_member_id'] = $member[2];
			$data['assessor']['assessment_result'] = CDataReportProvider::get_assessment_result_by_assessment_member_id($member[2]);
			$report = CDataReportProvider::calculate_result($data['assessor']['assessment_result'], $report, 'assessor');
		}
		if(!empty($data['verifier']['employee_id']))
		{
			$data['verifier'] = CDataReportProvider::get_employee_by_employee_id($data['verifier']['employee_id']);
			$data['verifier']['assessment_member_id'] = $member[3];
			$data['verifier']['assessment_result'] = CDataReportProvider::get_assessment_result_by_assessment_member_id($member[3]);
			$report = CDataReportProvider::calculate_result($data['verifier']['assessment_result'], $report, 'verifier');
		}
		$report = CDataReportProvider::determine_library_level($report);
		$report = CDataReportProvider::determine_color($report);

		$data['report'] = $report;


		$dataProvider=new CArrayDataProvider($data, array(
			'id'=> 'dataAssesment',
			'keyField'=>'comp_lib_code',
			'pagination'=>array(
				'pageSize'=>10,
			),
		));
		return $data;
	}

	function get_assessment_by_id($id)
	{
		$connection = Yii::app()->db;  
		$sql = "select * from assessment where assessment_id = $id";
		$dbCmd = $connection->createCommand($sql);

        $data = $dbCmd->queryAll();
		return $data[0];

	}
	
	function get_assessment_member_for_employee($assessment_id, $employee_id, $job_title_id)
	{
		$connection = Yii::app()->db;  
		$sql = "select * from assessment_member 
				where assessment_id = '$assessment_id' and 
					employee_id = '$employee_id' and 
					job_title_id = '$job_title_id' 
					order by assessment_type";
		$dbCmd = $connection->createCommand($sql);

        $data = $dbCmd->queryAll();
		return $data;
	}

	function get_employee_by_employee_id($id)
	{
		$connection = Yii::app()->db;  
		$sql = "select * from employee
					inner join 
					( select id as org_id, org_name from organization) organization using (org_id)
					inner join 
					(select id as job_title_id, job_title_name from job_title) job_title using (job_title_id)
				where employee_id = '$id'";
		$dbCmd = $connection->createCommand($sql);

        $data = $dbCmd->queryAll();
		return $data[0];
	
	}

	function get_assessment_result_by_assessment_member_id($assessment_member_id)
	{
		$connection = Yii::app()->db;  
		$sql = "select 
				comp_unit_code, comp_unit_name,
				comp_elem_code, comp_elem_name,
				comp_lib_code, comp_lib_name,
				comp_content_id, statement, 
				level, jlr, checked
				from assessment_result
				inner join 
				( select * from assessment_member where assmnt_mbr_id = '$assessment_member_id') m using (assmnt_mbr_id) 
				inner join 
				( select *, id as comp_content_id from competency_content) c using (comp_content_id)
				inner join competency_library l using (comp_lib_code)
				inner join competency_model   m using (comp_lib_code, job_title_id)
				inner join 
				(select *, id as job_title_id from job_title) j using (job_title_id)
				inner join competency_element e using (comp_elem_code)
				inner join competency_unit    u using (comp_unit_code)
				order by assmnt_mbr_id, comp_lib_code, level";


		$dbCmd = $connection->createCommand($sql);

        $data = $dbCmd->queryAll();

		return $data;
	}

	function calculate_result($data, $report, $assessor_type)
	{
		$comp_lib_code = '';
		$level = 0;
		$report_level = array();

		foreach($data as $i=>$result)
		{
			$level = $result['level'];
			if($comp_lib_code != $result['comp_lib_code'])
			{
				// sets lib path
				$comp_lib_code = $result['comp_lib_code'];
				$report[$comp_lib_code]['comp_unit_code'] = $result['comp_unit_code'];
                $report[$comp_lib_code]['comp_unit_name'] = $result['comp_unit_name'];
                $report[$comp_lib_code]['comp_elem_code'] = $result['comp_elem_code'];
                $report[$comp_lib_code]['comp_elem_name'] = $result['comp_elem_name'];
                $report[$comp_lib_code]['comp_lib_name']  = $result['comp_lib_name'];
                $report[$comp_lib_code]['jlr']  = $result['jlr'];
                $report[$comp_lib_code]['jlr_color']  = 'lightblue';
			}

			if($result['checked'] == 1)
			{
				$report[$comp_lib_code]['levels'][$level][$assessor_type] += 1;
			}

			if($assessor_type == 'assessor')
			{
				$report[$comp_lib_code]['levels'][$level]['jumlah_statement'] += 1;
			}
		}
		return $report;
	}

	function determine_library_level($report)
	{
		foreach($report as $lib_code=>$lib)
		{
			foreach($lib['levels'] as $level_id=>$result)
			{				
				if(isset($result['verifier']))								// if verifier exists use verifiers assessment
				{
					if($result['verifier']/$result['jumlah_statement'] >= 0.8)
						$report[$lib_code]['levels'][$level_id]['is_passed'] = 1;
					else
						$report[$lib_code]['levels'][$level_id]['is_passed'] = 0;
				}
				else if(isset($result['employee']))							// if verifier not exist and self assessment exist use min val between employee and assessor
				{
					$min_val = min(array($result['assessor'], $result['employee']));

					if($min_val/$result['jumlah_statement'] >= 0.8)
						$report[$lib_code]['levels'][$level_id]['is_passed'] = 1;
					else
						$report[$lib_code]['levels'][$level_id]['is_passed'] = 0;
				}
				else														// if verifier and sel assessment not exists use assessor's assessment
				{
					if($result['assessor']/$result['jumlah_statement'] >= 0.8)
						$report[$lib_code]['levels'][$level_id]['is_passed'] = 1;
					else
						$report[$lib_code]['levels'][$level_id]['is_passed'] = 0;
				}
			}

			
			foreach($report[$lib_code]['levels'] as $level_id=>$result)
			{		
				$is_passed = $report[$lib_code]['levels'][$level_id]['is_passed'];

				if($level_id == 1)
				{
					if($is_passed) $report[$lib_code]['level_passed'] = 1;
					else $report[$lib_code]['level_passed'] = 0;
				}
				else if(($is_passed == 1) and ($report[$lib_code]['levels'][($level_id-1)]['is_passed'] == 1))
				{
					$report[$lib_code]['level_passed'] = $level_id;
				}
				else
				{
					break;
				}
			}
		}

		return $report;
	}
	
	function determine_color($report)
	{
		foreach($report as $lib_code=>$lib)
		{
			if(($report[$lib_code]['level_passed'] - $report[$lib_code]['jlr']) <= -2)
				$report[$lib_code]['color'] = 'red';
			else if(($report[$lib_code]['level_passed'] - $report[$lib_code]['jlr']) == -1)
				$report[$lib_code]['color'] = 'yellow';
			else 
				$report[$lib_code]['color'] = 'green';
		}

		return $report;
	}
}
/*
		$sql = "
		select 
		comp_unit_name, comp_elem_name, comp_lib_name, comp_lib_code, comp_lib_name, 
		max(jlr) as jlr, 
		case when sum(level_achieved) = jlr then 1 else 0 end level_achieved, 
		case 
			when max(level_achieved)/jlr < 0.5 then 'red'
			when max(level_achieved)/jlr between 0.5 and 0.80 then 'yellow'
			else 'green'
		end color
		from 
		(
			select 
			comp_lib_code, jlr, level, target, achieved, 
			case when achieved = target then level else 0 end as level_achieved
			from 
			(
				select comp_lib_code, level, jlr, count(comp_content_id) as target, 
				sum(checked) as achieved
				from 
				(
					select comp_lib_code, level, jlr, checked, competency_content.comp_content_id, assmnt_mbr_id 
					from
					(
						select id as comp_content_id, comp_lib_code, level, jlr, assmnt_mbr_id
						from competency_content
						inner join competency_model using (comp_lib_code)
						inner join assessment_member using (job_title_id)
					) competency_content
					left join assessment_result using (comp_content_id, assmnt_mbr_id)
					where
					level <= jlr 
					and assmnt_mbr_id = $assessment_member_id
					order by jlr, comp_content_id, level 
				) a
				group by comp_lib_code,level
			) b
		) c
		inner join competency_library using (comp_lib_code)
		inner join competency_element using (comp_elem_code)
		inner join competency_unit using (comp_unit_code)
		group by comp_lib_code
		";
*/
?>
